using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Windows.Forms;
using ctsu.Data;

namespace ctsu.Admin.Forms
{
	public partial class TransferFromSstToTestReference : Form
	{

		public TransferFromSstToTestReference()
		{
			InitializeComponent();
		}

		private void TransferFromSstToTestReference_Load(object sender, EventArgs e)
		{

		}

		private void btnGetFile_Click(object sender, EventArgs e)
		{
			if (DialogResult.OK == openFileDialog1.ShowDialog(this))
			{
				textBox1.Text = openFileDialog1.FileName;
				GetBatchList();
			}
		}


		private void GetBatchList()
		{
			StreamReader sr = File.OpenText(textBox1.Text);
			string s = sr.ReadToEnd();
			string[] sep = { "\r", "\n" };
			string[] a = s.Split(sep, StringSplitOptions.RemoveEmptyEntries);
			sr.Close();

			foreach (string sBatch in a)
				lstBatch.Items.Add(sBatch);
		}

		private void btnCreateFilesOnly_Click(object sender, EventArgs e)
		{
			ProcessBatchList(true);
		}

		private void ProcessBatchList(bool filesOnly)
		{
			StringBuilder sb = new StringBuilder();
			string file = "SstTransferExceptions.txt";
			StreamWriter sw = new StreamWriter(file, false);

			string file2 = "SstTransferUpdates.txt";
			StreamWriter sw2 = new StreamWriter(file2, false);

			Hashtable ht = new Hashtable();
			//string message = "";
			string sBatch = "";
			string newBatchNum = "";
			bool isBatchRecords = false;
			string sConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
			SqlConnection conn = new SqlConnection(sConn);

			try
			{
				IDataReader reader = null;
				foreach (object obj in lstBatch.Items)
				{
					sBatch = obj.ToString();
					isBatchRecords = false;

					string sql = "SELECT ID,TestID,Location,Disposition,Notes FROM Sst WHERE BatchNum=" + sBatch;
					reader = DataAccess.GetDataReader(sql);
					while (reader.Read())
					{
						if (!filesOnly)
						{
							if (!isBatchRecords)
								newBatchNum = CreateNewBatch(sBatch);
						}
						isBatchRecords = true;

						int id = reader.GetInt32(0);

						if (reader.IsDBNull(1))
						{
							sw.WriteLine("Batch: " + sBatch + ", sstID: " + id + " - no crema reference");
							continue;
						}
						int cremaID = reader.GetInt32(1);
						if (cremaID < 1)
						{
							sw.WriteLine("Batch: " + sBatch + ", sstID: " + id + " - no crema reference");
							continue;
						}
						if (ht.Contains(cremaID))
						{
							string sDupeBatch = (ht[cremaID].ToString());
							sw.WriteLine("Batch: " + sBatch + ", sstID: " + id + " - duplicate crema reference from batch: " + sDupeBatch + ", cremaID: " + cremaID);
							continue;
						}
						ht.Add(cremaID, sBatch);

						if (reader.IsDBNull(2))
						{
							sw.WriteLine("Batch: " + sBatch + ", sstID: " + id + " - no location");
							continue;
						}
						string location = reader.GetString(2);
						if (location.Length == 0)
						{
							sw.WriteLine("Batch: " + sBatch + ", sstID: " + id + " - no location");
							continue;
						}
						string disposition = reader.GetString(3);
						string notes = reader.GetString(4);

						if (disposition.Length > 0)
						{
							if (notes.Length > 0)
								disposition += ", " + notes;
						}
						else
						{
							if (notes.Length > 0)
								disposition = notes;
						}

						if (!filesOnly)
						{
							if (TransferToTestReference(newBatchNum, sBatch, cremaID, location, disposition))
							{
								UpdateSst(id, newBatchNum);
							}
						}
						sw2.WriteLine("sstBatch: " + sBatch + ", sstID: " + id +
							", Test Reference Batch: " + newBatchNum + ", cremaID: " + cremaID + ", location: " + location);
						sw2.WriteLine("    Notes: " + disposition);
					}
					if (!isBatchRecords)
						sw.WriteLine("No items found for batch " + sBatch);

				}
				MessageBox.Show("Create files completed!");
			}
			catch (Exception ex)
			{
				sb.Append("Could not transfer.");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				MessageBox.Show(this, sb.ToString(), "Transfer Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			finally
			{
				sw.Close();
				sw2.Close();
			}

		}

		private void UpdateSst(int id, string newBatchNum)
		{
			string sql = "UPDATE Sst SET OutDate=@OutDate, Location=@Location, Disposition=@Disposition WHERE ID=" + id;
			string sConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
			SqlConnection conn = new SqlConnection(sConn);
			try
			{
				conn.Open();

				SqlCommand cmd = new SqlCommand(sql, conn);
				SqlParameter aParam = new SqlParameter("@OutDate", DateTime.Today.Date);
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@Location", "");
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@Disposition", "Transfered to Test Reference, batch: " + newBatchNum);
				cmd.Parameters.Add(aParam);

				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				string message = ex.Message;
				MessageBox.Show("Could not update sst for id: " + id);
			}
			finally
			{
				conn.Close();
			}

		}

		private bool TransferToTestReference(string newBatchNum, string sBatch, int cremaID, string location, string disposition)
		{
			string sstDispositionAndNotes = "";
			if (disposition.Length > 0)
				sstDispositionAndNotes = " - sst disposition and notes: " + disposition;
			string notes = "transfered from sst batch: " + sBatch + sstDispositionAndNotes;
			string sql = "INSERT INTO TestReference (ID, BatchNum, Location, Notes) VALUES(@ID, @BatchNum, @Location, @Notes)";
			string sConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
			SqlConnection conn = new SqlConnection(sConn);
			try
			{
				conn.Open();

				SqlCommand cmd = new SqlCommand(sql, conn);
				SqlParameter aParam = new SqlParameter("@ID", cremaID);
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@BatchNum", newBatchNum);
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@Location", location);
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@Notes", notes);
				cmd.Parameters.Add(aParam);

				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				string message = ex.Message;
				MessageBox.Show("Could not create new test reference item for cremaID: " + cremaID);
				return false;
			}
			finally
			{
				conn.Close();
			}
		}

		private string CreateNewBatch(string sstBatch)
		{
			string sql = "INSERT INTO TestReferenceBatch (BatchDate,BatchName,CreatedBy,CreatedWhen) VALUES(@BatchDate,@BatchName,@CreatedBy,@CreatedWhen)";
			string sConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
			SqlConnection conn = new SqlConnection(sConn);

			try
			{
				conn.Open();

				SqlCommand cmd = new SqlCommand(sql, conn);
				SqlParameter aParam = new SqlParameter("@BatchDate", DateTime.Today.Date);
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@BatchName", "From sstBatch: " + sstBatch);
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@CreatedBy", "Joe Rezuke");
				cmd.Parameters.Add(aParam);
				aParam = new SqlParameter("@CreatedWhen", DateTime.Today.Date);
				cmd.Parameters.Add(aParam);

				cmd.ExecuteNonQuery();

				cmd.CommandText = "SELECT @@IDENTITY";
				return cmd.ExecuteScalar().ToString();
			}
			catch (Exception ex)
			{
				string message = ex.Message;
				MessageBox.Show("Could not create new batch number");
				return "";
			}
			finally
			{
				conn.Close();
			}
		}

		private void btnLoad_Click(object sender, EventArgs e)
		{
			GetBatchList();
		}

		private void btnTransfer_Click(object sender, EventArgs e)
		{
			ProcessBatchList(false);
		}


	}
}